﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Threading;
using System.Windows.Forms;
using Aspose.Cells;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        SQLiteConnection conn = null;
        string dbPath = "Data Source =" + Environment.CurrentDirectory + "/myDB.db";
        string tableName = "";
        public Form1()
        {
            InitializeComponent();
            InitListView1();
            InitListView2();
            InitDB();
        }

        private void InitListView1()
        {
            listView1.Clear();
            this.listView1.View = View.Details;
            //this.listView1.LabelEdit = true;
            this.listView1.AllowColumnReorder = true;
            this.listView1.CheckBoxes = true;
            this.listView1.FullRowSelect = true;
            this.listView1.GridLines = true;
            listView1.Columns.Add("ID", 50);
            listView1.Columns.Add("Name", 500);
            listView1.Columns.Add("Src", 500);

        }
        private void InitListView2()
        {
            this.listView2.View = View.Details;
            //this.listView2.LabelEdit = true;
            this.listView2.AllowColumnReorder = true;
            this.listView2.CheckBoxes = true;
            this.listView2.FullRowSelect = true;
            this.listView2.GridLines = true;
            listView2.Columns.Add("ID", 50);
            listView2.Columns.Add("Name", 500);
            listView2.Columns.Add("Src", 500);
        }
        private void button4_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Equals(""))
            {
                MyDialog dialog = new MyDialog("请选择目标路径", false, true);
                dialog.Show();
                return;
            }
            string TargetPath = textBox1.Text;
            foreach (ListViewItem item in listView2.Items)
            {
                try
                {
                    System.IO.File.Copy(item.SubItems[2].Text + "\\" + item.SubItems[1].Text, TargetPath + "\\" + item.SubItems[1].Text,true);
                }
                catch (Exception ee)
                {
                    MyDialog dialog = new MyDialog("复制" + item.SubItems[2].Text + "\\" + item.SubItems[1].Text + "错误", false, true);
                    dialog.Show();
                    break;
                }
            }
            MyDialog dialog1 = new MyDialog("复制完成", false, true);
            dialog1.Show();
        }

        void InitDB()
        {
            openDBCon();
            SQLiteCommand tableCmd = conn.CreateCommand();
            tableCmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table'";
            SQLiteDataReader reader = tableCmd.ExecuteReader();
            while (reader.Read())
            {
                comboBox1.Items.Add(reader["name"].ToString());
            }
            closeDBCon();
        }

        private void addDB(string tableName)
        {
            SQLiteCommand ExistCmd = conn.CreateCommand();
            ExistCmd.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='music'";
            if (0 == Convert.ToInt32(ExistCmd.ExecuteScalar()))
            {
                string sql = "CREATE TABLE IF NOT EXISTS music (id integer, name varchar(500) ,src varchar(600));";//建表语句  
                SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn);
                cmdCreateTable.ExecuteNonQuery();//如果表不存在，创建数据表  
            }
        }
        private void openDBCon()
        {
                //string dbPath = "Data Source =" + Environment.CurrentDirectory + "/myDB.db";
                //string dbPath = "Data Source =" + "D:/myDB.db";
                conn = new SQLiteConnection(dbPath);//创建数据库实例，指定文件位置  
                conn.Open();//打开数据库，若文件不存在会自动创建  

        }
        void closeDBCon()
        {
                conn.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (tableName.Equals(""))
            {
                MyDialog dialog = new MyDialog("请选择DB", false, true);
                dialog.Show();
                return;
            }
            if (folderBrowserDialog == null)
            {
                folderBrowserDialog = new FolderBrowserDialog();
            }
            if (folderBrowserDialog.ShowDialog() == DialogResult.OK)
            {

                openDBCon();

                //获得最后ID
                int ID = 0;

                
                //SQLiteCommand cmdCount = new SQLiteCommand(conn);
                //string a = tableName;
                //cmdCount.CommandText = @"SELECT COUNT(*) FROM " + tableName;
                // if (cmdCount.ExecuteNonQuery() != 0)
                //{
                SQLiteCommand cmdFinal = new SQLiteCommand(conn);
                //cmdFinal.CommandText = @"SELECT * FROM " + tableName + " ORDER BY id DESC LIMIT (0,1)";
                cmdFinal.CommandText = @"SELECT * FROM " + tableName + " ORDER BY id DESC LIMIT (0,1)";
                try
                {
                    SQLiteDataReader reader = cmdFinal.ExecuteReader();
                    while (reader.Read())
                    {
                        ID = Convert.ToInt32(reader["id"]);
                    }
                }
                catch (Exception ee)
                {
                    ID = 0;
                }
               

                

                //插入文件夹数据
                SQLiteCommand cmdInsert = new SQLiteCommand(conn);
                string folder = folderBrowserDialog.SelectedPath;
                System.IO.DirectoryInfo directoryInfo = new System.IO.DirectoryInfo(folder);
                foreach (var file in directoryInfo.GetFiles())
                {
                    try
                    {
                        cmdInsert.CommandText = "INSERT INTO " + tableName + " VALUES(" + ++ID + ",'" + file.Name + "','" + folder + "')";
                        cmdInsert.ExecuteNonQuery();
                    }
                    catch (Exception ee)
                    {
                        MyDialog dialog = new MyDialog("添加" + file.Name+"失败", false, true);
                        dialog.Show();
                    }
            
                }

                closeDBCon();
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            tableName = comboBox1.Text;
            InitListView1();
            openDBCon();
            SQLiteCommand FindAllCmd = conn.CreateCommand();
            FindAllCmd.CommandText = @"SELECT * FROM " + tableName;
            FindAllCmd.CommandType = System.Data.CommandType.Text;
            SQLiteDataReader reader = FindAllCmd.ExecuteReader();
            while (reader.Read())
            {
                ListViewItem item = new ListViewItem(reader["id"] + "", Convert.ToInt32(reader["id"]));
                item.SubItems.Add(reader["name"].ToString());
                string s = reader["name"].ToString();
                item.SubItems.Add(reader["src"].ToString());
                listView1.Items.Add(item);

                //int data = Convert.ToInt32(reader["id"]);
            }
            closeDBCon();
            //listView1.Sort();
            this.Controls.Add(listView1);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog == null)
            {
                folderBrowserDialog = new FolderBrowserDialog();
            }
            folderBrowserDialog.ShowDialog();
            textBox1.Text = folderBrowserDialog.SelectedPath;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (tableName.Equals(""))
            {
                MyDialog dialog = new MyDialog("请选择DB", false, true);
                dialog.Show();
                return;
            }
            if (fileDialog == null)
            {
                fileDialog = new OpenFileDialog();
            }
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                openDBCon();

                //获得最后ID
                int ID = 0;
                SQLiteCommand cmdFinal = new SQLiteCommand(conn);
                cmdFinal.CommandText = @"SELECT * FROM " + tableName + " ORDER BY id DESC LIMIT (0,1)";
                try
                {
                    SQLiteDataReader reader = cmdFinal.ExecuteReader();
                    while (reader.Read())
                    {
                        ID = Convert.ToInt32(reader["id"]);
                    }
                }
                catch (Exception ee)
                {
                    ID = 0;
                }




                //插入文件夹数据
                string fileName = fileDialog.FileName.Substring(fileDialog.FileName.LastIndexOf('\\') + 1, fileDialog.FileName.Length - fileDialog.FileName.LastIndexOf('\\') - 1);
                string folder = fileDialog.FileName.Substring(0, fileDialog.FileName.LastIndexOf('\\'));
                SQLiteCommand cmdInsert = new SQLiteCommand(conn);
                try
                {
                    cmdInsert.CommandText = "INSERT INTO " + tableName + " VALUES(" + ++ID + ",'" + fileName + "','" + folder + "')";
                    cmdInsert.ExecuteNonQuery();
                    MyDialog dialog = new MyDialog("添加" + fileName + "成功，查看请重新选择DB。", false, true);
                    dialog.Show();
                }
                catch (Exception ee)
                {
                    MyDialog dialog = new MyDialog("添加" + fileName+"失败", false, true);
                    dialog.Show();
                }

                closeDBCon();

            }
        }

        private void button7_Click(object sender, EventArgs e)
        {

            try
            {
                if (!textBox2.Text.Equals(""))
                {
                    openDBCon();
                    string sql = "CREATE TABLE IF NOT EXISTS " + textBox2.Text + " (id integer, name varchar(500) ,src varchar(600));";//建表语句  
                    SQLiteCommand cmdCreateTable = conn.CreateCommand();
                    cmdCreateTable.CommandText = sql;
                    cmdCreateTable.ExecuteNonQuery();//如果表不存在，创建数据表 
                    closeDBCon();
                    comboBox1.Items.Clear();
                    InitDB();
                    MyDialog dialog = new MyDialog("DB添加成功", false, true);
                    dialog.Show();
                    return;
                }
            }
            catch (Exception ee)
            {
                MyDialog dialog = new MyDialog("DB添加失败", false, true);
                dialog.Show();
                return;
            }

        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (tableName.Equals(""))
            {
                MyDialog dialog = new MyDialog("请选择DB", false, true);
                dialog.Show();
                return;
            }
            string numberString = textBox3.Text;
            string[] numbers=new string[100];
            try
            {
                numbers = numberString.Split(',');
            }
            catch (Exception ee)
            {
                MyDialog dialog = new MyDialog("歌曲名请用英语下的逗号（,）分开", false, true);
                dialog.Show();
                return;
            }
            openDBCon();
            string sql;
            SQLiteCommand cmdFind;
            SQLiteDataReader reader;
            int count = listView1.Items.Count;
            for (int i = 0; i < numbers.Length; i++)
            {
                try {
                    if (Convert.ToInt16(numbers[i]) < 1 || Convert.ToInt16(numbers[i]) > count)
                    {
                        MyDialog dialog = new MyDialog("数字必须在可用范围内", false, true);
                        dialog.Show();
                        break;
                    }
                }
                catch (Exception ee)
                {
                    MyDialog dialog = new MyDialog("请按照格式并且输入数字", false, true);
                    dialog.Show();
                    break;
                }
                sql = "SELECT *  FROM " + tableName + " WHERE id=" + numbers[i];
                cmdFind = new SQLiteCommand(conn);
                cmdFind.CommandText = sql;
                reader = cmdFind.ExecuteReader(); 
                reader.Read();
                ListViewItem item = new ListViewItem(reader["id"] + "");
                item.SubItems.Add(reader["name"].ToString());
                item.SubItems.Add(reader["src"].ToString());
                listView2.Items.Add(item);
            }
            this.Controls.Add(listView1);
            closeDBCon();
        }

        private void label6_Click(object sender, EventArgs e)
        {

        }

        private void button9_Click(object sender, EventArgs e)
        {
            //int i = listView2.Items.Count;
            foreach (ListViewItem item in listView1.CheckedItems)
            {
                //i++;
                ListViewItem item1 = new ListViewItem(item.Text);
                item1.SubItems.Add(item.SubItems[1]);
                item1.SubItems.Add(item.SubItems[2]);
                listView2.Items.Add(item1);
            }
            this.Controls.Add(listView2);
            label11.Text = listView2.Items.Count.ToString();
        }

        private void button11_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listView1.Items)
            {
                if (!item.Checked)
                {
                    item.Checked = true;
                }
            }
        }

        private void button12_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listView1.Items)
            {
                if (item.Checked)
                {
                    item.Checked = false;
                }
            }
        }

        private void button10_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listView2.CheckedItems)
            {
                item.Remove();
            }
            label11.Text = listView2.Items.Count.ToString();
        }

        private void button14_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listView2.Items)
            {
                if (!item.Checked)
                {
                    item.Checked = true;
                }
            }
        }

        private void button13_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listView2.Items)
            {
                if (item.Checked)
                {
                    item.Checked = false;
                }
            }
        }
        public void ReportToExcel(ListView list, List<int> ColumnWidth, string ReportTitleName)
        {
            //获取用户选择的excel文件名称
            string path;
            SaveFileDialog savefile = new SaveFileDialog();
            savefile.Filter = "Excel files(*.xls)|*.xls";
            if (savefile.ShowDialog() == DialogResult.OK)
            {
                //获取保存路径
                path = savefile.FileName;
                Workbook wb = new Workbook();
                Worksheet ws = wb.Worksheets[0];
                Cells cell = ws.Cells;
                //定义并获取导出的数据源
                string[,] _ReportDt = new string[list.Items.Count, list.Columns.Count];
                for (int i = 0; i < list.Items.Count; i++)
                {
                    for (int j = 0; j < list.Columns.Count; j++)
                    {
                        _ReportDt[i, j] = list.Items[i].SubItems[j].Text.ToString();
                    }
                }
                //合并第一行单元格
                Range range = cell.CreateRange(0, 0, 1, list.Columns.Count);
                range.Merge();
                cell["A1"].PutValue(ReportTitleName); //标题

                //设置行高
                cell.SetRowHeight(0, 20);

                //设置字体样式
                Style style1 = wb.Styles[wb.Styles.Add()];
                style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style1.Font.Name = "宋体";
                style1.Font.IsBold = true;//设置粗体
                style1.Font.Size = 12;//设置字体大小

                Style style2 = wb.Styles[wb.Styles.Add()];
                style2.HorizontalAlignment = TextAlignmentType.Center;
                style2.Font.Size = 10;

                //给单元格关联样式
                cell["A1"].SetStyle(style1); //报表名字 样式

                //设置Execl列名
                for (int i = 0; i < list.Columns.Count; i++)
                {
                    cell[1, i].PutValue(list.Columns[i].Text);
                    cell[1, i].SetStyle(style2);
                }

                //设置单元格内容
                int posStart = 2;
                for (int i = 0; i < list.Items.Count; i++)
                {
                    for (int j = 0; j < list.Columns.Count; j++)
                    {
                        cell[i + posStart, j].PutValue(_ReportDt[i, j].ToString());
                        cell[i + posStart, j].SetStyle(style2);
                    }
                }

                //设置列宽
                for (int i = 0; i < list.Columns.Count; i++)
                {
                    cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString()));
                }
                //保存excel表格
                wb.Save(path);
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            try
            {
                List<int> list = new List<int>() { 5, 60, 60 };
                ReportToExcel(listView1, list, tableName + "歌曲");
                MyDialog dialog = new MyDialog("Excel导出完成", false, true);
                dialog.Show();
            }
            catch (Exception ee)
            {
                MyDialog dialog = new MyDialog("Excel导出失败", false, true);
                dialog.Show();
            }
          
        }

        private void listView2_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }
    }
}
